<?php
/*
    OpenLib
    Copyright (C) 2011  Michael Gukov, Pham Thi Thuy Duong

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/


namespace App\Models;

use Sun\Sun;

class User implements \Sun\IModel {
    public $db;

    public function __construct() {
        $this->db = new DB();
    }

    public function UserTypes() {
        $result = $this->db->Table("user_type");
        return $result->fetchAll(DB::FETCH_NUM);
    }

    public function UserCount($cond = "true") {
        $query = "SELECT count(*) FROM \"user\"
            WHERE {$cond}";
        $num = $this->db->SafeQuery($query)->fetch(DB::FETCH_NUM);
        return $num[0];
    }

    public function UserList($offset = 0, $count = 0, $cond = "true", $sort = "user_id") {
        $query = "SELECT user_id \"Ид\", user_type.name \"Тип\", login \"Логин\", fullname \"ФИО\",
                    to_char(reg_date, 'dd.mm.yyyy') \"Дата регистрации\",
                    to_char(reg_comp, 'dd.mm.yyyy') \"Дата окончания\"
                FROM \"user\", user_type WHERE \"user\".type_id = user_type.type_id
                    AND {$cond}
                ORDER BY {$sort}"
                    .(!empty($count) ? " LIMIT {$count} OFFSET {$offset}" : "");
        return $this->db->SafeQuery($query)->fetchAll(DB::FETCH_ASSOC);
    }

    public function ReaderList($offset = 0, $count = 0, $cond = "true", $sort = "user_id") {

        $query = "SELECT user_id \"Ид\", login \"Логин\", fullname \"ФИО\",
                to_char(reg_date, 'dd.mm.yyyy') \"Дата регистрации\",
                to_char(reg_comp, 'dd.mm.yyyy') \"Дата окончания\"
            FROM \"user\" JOIN user_type USING(type_id)
            WHERE sense IN ('READ', 'TEACH') AND {$cond} ORDER BY {$sort}"
                .(!empty($count) ? " LIMIT {$count} OFFSET {$offset}" : "");
        return $this->db->SafeQuery($query);
    }

    public function ReaderCount($cond = "true") {
        $query = "SELECT count(*) FROM \"user\" join user_type using(type_id)
            WHERE sense IN ('READ', 'TEACH') AND {$cond}";
        $num = $this->db->SafeQuery($query)->fetch(DB::FETCH_NUM);
        return $num[0];
    }

    public function DebetorList($offset = 0, $count = 0, $cond = "true", $sort = "user_id") {
        $query = "SELECT \"user\".user_id \"Ид\", login \"Логин\", fullname \"ФИО\",
                    to_char(reg_date, 'dd.mm.yyyy') \"Дата регистрации\",
                    to_char(reg_comp, 'dd.mm.yyyy') \"Дата окончания\"
                FROM \"user\" JOIN user_type USING(type_id)
                    JOIN \"order\" USING(user_id)
                    JOIN issue USING(order_id)
                WHERE sense IN ('READ', 'TEACH') AND {$cond}
                    AND ret_date < CURRENT_DATE AND stat_id = 2 ORDER BY {$sort}"
                    .(!empty($count) ? " LIMIT {$count} OFFSET {$offset}" : "");
        return $this->db->SafeQuery($query);
    }

    public function DebetorCount($cond = "true") {
        $query = "SELECT count(*)
                FROM \"user\" JOIN user_type USING(type_id)
                    JOIN \"order\" USING(user_id)
                    JOIN issue USING(order_id)
                WHERE sense IN ('READ', 'TEACH') AND {$cond}
                    AND ret_date < CURRENT_DATE AND stat_id = 2"
                    .(!empty($count) ? " LIMIT {$count} OFFSET {$offset}" : "");
        $num = $this->db->SafeQuery($query)->fetch(DB::FETCH_NUM);
        return $num[0];
    }

    public function UserInfo($id) {
        $query = "SELECT user_id, user_type.name \"type\", login , fullname,
                \"user\".type_id, pass,
                to_char(reg_date, 'dd.mm.yyyy') \"reg_date\",
                to_char(reg_comp, 'dd.mm.yyyy') \"reg_comp\"
            FROM \"user\", user_type WHERE \"user\".type_id = user_type.type_id AND user_id = '{$id}'";
        return $this->db->SafeQuery($query)->fetch(DB::FETCH_ASSOC);
    }

    public function UserProfile($id) {
        $query = "SELECT user_id \"Ид\", fullname \"ФИО\", login \"Логин\", user_type.name \"Тип\",
                to_char(reg_date, 'dd.mm.yyyy') \"Дата регистрации\",
                to_char(reg_comp, 'dd.mm.yyyy') \"Дата окончания\"
            FROM \"user\" JOIN user_type using(type_id) WHERE user_id = $id";
        return $this->db->SafeQuery($query)->fetch(DB::FETCH_ASSOC);
    }

    public function FindUser($cond) {
        $query = "SELECT user_id \"Ид\", user_type.name \"Тип\", login \"Логин\", fullname \"ФИО\"
                FROM \"user\", user_type WHERE \"user\".type_id = user_type.type_id AND (true {$cond}) ORDER BY user_id";
        return $this->db->SafeQuery($query)->fetchAll(DB::FETCH_ASSOC);
    }

    public function AddUser($p) {
        $id = $this->GetCurrentId();

        $query = "UPDATE user_curr_id SET id = (SELECT nextval('user_id'))";
        $this->db->SafeExec($query);

        $query = "INSERT INTO \"user\"(user_id, fullname, login, pass, type_id, reg_date, reg_comp)
            VALUES({$id}, '{$p['fullname']}', '{$p['login']}',
            '{$p['pass']}', {$p['type_id']}, '{$p['reg_date']}', '{$p['reg_comp']}')";

        return $this->db->SafeExec($query);
    }

    public function AltUser($p) {
        $query = "UPDATE \"user\" SET
            fullname = '{$p['fullname']}',
            login = '{$p['login']}',
            pass = '{$p['pass']}',
            type_id = {$p['type_id']},
            reg_date = ".$this->db->quote($p['reg_date']).",
            reg_comp = ".$this->db->quote($p['reg_comp'])."
            WHERE user_id = {$p['user_id']}";
        return $this->db->SafeExec($query);
    }

    public function DelUser($id) {
        $query = "DELETE FROM \"user\" WHERE user_id = ".$id;
        return $this->db->SafeExec($query);
    }



    public function GetIssues($id) {
        $query = "SELECT issue.issue_id, issue.item_id, \"order\".edit_id,
                to_char(iss_date, 'dd.mm.yyyy') \"iss_date\",
                to_char(issue.ret_date, 'dd.mm.yyyy') \"ret_date\", issue.order_id
            FROM issue
                LEFT JOIN \"order\" using(order_id)
            WHERE user_id = {$id} AND \"order\".stat_id = 2";
        return $this->db->SafeQuery($query)->fetchAll(DB::FETCH_ASSOC);
    }

    public function GetReturns($id) {
        $query = "SELECT * FROM \"return\" WHERE issue_id = {$id}";
        return $this->db->SafeQuery($query)->fetchAll(DB::FETCH_ASSOC);
    }

    public function GetOrders($id) {
        $query = "SELECT * FROM \"order\" o
            WHERE o.stat_id = 1 AND user_id = '{$id}'";
        return $this->db->SafeQuery($query)->fetchAll(DB::FETCH_ASSOC);
    }

    public function CheckRegistr($id) {

    }


    public function GetCurrentId() {
        $query = "SELECT * FROM user_curr_id";
        $id = $this->db->SafeQuery($query)->fetch();
        return $id[0];
    }
}
